"""
Database Utilities
=================

Database connection management and CRUD operations utilities.
Provides a unified interface for database operations with error handling.
"""

import logging
from contextlib import contextmanager
from sqlalchemy.exc import SQLAlchemyError, IntegrityError
from sqlalchemy.orm.exc import NoResultFound
from typing import Optional, Dict, Any, List, Type, Union
from flask_sqlalchemy import SQLAlchemy

# Initialize logger
logger = logging.getLogger(__name__)

class DatabaseManager:
    """
    Database manager class providing CRUD operations and connection management.
    
    This class offers a high-level interface for database operations with
    built-in error handling, connection management, and logging.
    """
    
    def __init__(self, db: SQLAlchemy):
        """
        Initialize database manager.
        
        Args:
            db: SQLAlchemy database instance
        """
        self.db = db
    
    @contextmanager
    def get_session(self):
        """
        Context manager for database sessions.
        
        Yields:
            Database session with automatic commit/rollback
        """
        session = self.db.session
        try:
            yield session
            session.commit()
        except Exception as e:
            session.rollback()
            logger.error(f"Database session error: {str(e)}")
            raise
        finally:
            session.close()
    
    def create(self, model_instance, commit: bool = True) -> bool:
        """
        Create a new record in the database.
        
        Args:
            model_instance: Model instance to create
            commit: Whether to commit immediately
        
        Returns:
            bool: True if creation successful, False otherwise
        
        Raises:
            IntegrityError: If database integrity constraints are violated
        """
        try:
            with self.get_session() as session:
                session.add(model_instance)
                if commit:
                    session.commit()
                logger.info(f"Created {model_instance.__class__.__name__} record")
                return True
        except IntegrityError as e:
            logger.error(f"Integrity error creating record: {str(e)}")
            raise
        except SQLAlchemyError as e:
            logger.error(f"Database error creating record: {str(e)}")
            return False
    
    def get_by_id(self, model_class: Type, record_id: Any) -> Optional[Any]:
        """
        Retrieve a record by its primary key.
        
        Args:
            model_class: Model class to query
            record_id: Primary key value
        
        Returns:
            Model instance or None if not found
        """
        try:
            with self.get_session() as session:
                return session.query(model_class).get(record_id)
        except SQLAlchemyError as e:
            logger.error(f"Database error retrieving record: {str(e)}")
            return None
    
    def get_by_field(self, model_class: Type, field_name: str, field_value: Any) -> Optional[Any]:
        """
        Retrieve a record by a specific field.
        
        Args:
            model_class: Model class to query
            field_name: Field name to filter by
            field_value: Field value to match
        
        Returns:
            Model instance or None if not found
        """
        try:
            with self.get_session() as session:
                return session.query(model_class).filter(
                    getattr(model_class, field_name) == field_value
                ).first()
        except SQLAlchemyError as e:
            logger.error(f"Database error retrieving record: {str(e)}")
            return None
    
    def get_all(self, model_class: Type, page: int = 1, per_page: int = 10) -> Dict[str, Any]:
        """
        Retrieve all records with pagination.
        
        Args:
            model_class: Model class to query
            page: Page number (1-indexed)
            per_page: Number of records per page
        
        Returns:
            Dictionary with pagination data and records
        """
        try:
            with self.get_session() as session:
                query = session.query(model_class)
                
                # Calculate pagination
                total = query.count()
                offset = (page - 1) * per_page
                
                records = query.offset(offset).limit(per_page).all()
                
                return {
                    'records': records,
                    'total': total,
                    'page': page,
                    'per_page': per_page,
                    'pages': (total + per_page - 1) // per_page
                }
        except SQLAlchemyError as e:
            logger.error(f"Database error retrieving records: {str(e)}")
            return {'records': [], 'total': 0, 'page': 1, 'per_page': per_page, 'pages': 0}
    
    def filter_by(self, model_class: Type, filters: Dict[str, Any], 
                  page: int = 1, per_page: int = 10) -> Dict[str, Any]:
        """
        Retrieve records filtered by specified criteria.
        
        Args:
            model_class: Model class to query
            filters: Dictionary of filter criteria
            page: Page number (1-indexed)
            per_page: Number of records per page
        
        Returns:
            Dictionary with pagination data and filtered records
        """
        try:
            with self.get_session() as session:
                query = session.query(model_class)
                
                # Apply filters
                for field, value in filters.items():
                    if hasattr(model_class, field):
                        query = query.filter(getattr(model_class, field) == value)
                
                # Calculate pagination
                total = query.count()
                offset = (page - 1) * per_page
                
                records = query.offset(offset).limit(per_page).all()
                
                return {
                    'records': records,
                    'total': total,
                    'page': page,
                    'per_page': per_page,
                    'pages': (total + per_page - 1) // per_page
                }
        except SQLAlchemyError as e:
            logger.error(f"Database error filtering records: {str(e)}")
            return {'records': [], 'total': 0, 'page': 1, 'per_page': per_page, 'pages': 0}
    
    def update(self, model_instance, update_data: Dict[str, Any], commit: bool = True) -> bool:
        """
        Update an existing record.
        
        Args:
            model_instance: Model instance to update
            update_data: Dictionary of fields to update
            commit: Whether to commit immediately
        
        Returns:
            bool: True if update successful, False otherwise
        """
        try:
            with self.get_session() as session:
                # Merge the instance with the session
                session.merge(model_instance)
                
                # Update fields
                for field, value in update_data.items():
                    if hasattr(model_instance, field):
                        setattr(model_instance, field, value)
                
                if commit:
                    session.commit()
                
                logger.info(f"Updated {model_instance.__class__.__name__} record")
                return True
        except SQLAlchemyError as e:
            logger.error(f"Database error updating record: {str(e)}")
            return False
    
    def update_by_id(self, model_class: Type, record_id: Any, 
                     update_data: Dict[str, Any]) -> bool:
        """
        Update a record by its primary key.
        
        Args:
            model_class: Model class to update
            record_id: Primary key value
            update_data: Dictionary of fields to update
        
        Returns:
            bool: True if update successful, False otherwise
        """
        try:
            with self.get_session() as session:
                record = session.query(model_class).get(record_id)
                if not record:
                    return False
                
                for field, value in update_data.items():
                    if hasattr(record, field):
                        setattr(record, field, value)
                
                session.commit()
                logger.info(f"Updated {model_class.__name__} record with ID {record_id}")
                return True
        except SQLAlchemyError as e:
            logger.error(f"Database error updating record: {str(e)}")
            return False
    
    def delete(self, model_instance, commit: bool = True) -> bool:
        """
        Delete a record from the database.
        
        Args:
            model_instance: Model instance to delete
            commit: Whether to commit immediately
        
        Returns:
            bool: True if deletion successful, False otherwise
        """
        try:
            with self.get_session() as session:
                session.delete(model_instance)
                if commit:
                    session.commit()
                logger.info(f"Deleted {model_instance.__class__.__name__} record")
                return True
        except SQLAlchemyError as e:
            logger.error(f"Database error deleting record: {str(e)}")
            return False
    
    def delete_by_id(self, model_class: Type, record_id: Any) -> bool:
        """
        Delete a record by its primary key.
        
        Args:
            model_class: Model class to delete from
            record_id: Primary key value
        
        Returns:
            bool: True if deletion successful, False otherwise
        """
        try:
            with self.get_session() as session:
                record = session.query(model_class).get(record_id)
                if not record:
                    return False
                
                session.delete(record)
                session.commit()
                logger.info(f"Deleted {model_class.__name__} record with ID {record_id}")
                return True
        except SQLAlchemyError as e:
            logger.error(f"Database error deleting record: {str(e)}")
            return False
    
    def execute_raw_query(self, query: str, params: Optional[Dict] = None) -> List:
        """
        Execute a raw SQL query.
        
        Args:
            query: Raw SQL query string
            params: Query parameters
        
        Returns:
            List of query results
        """
        try:
            with self.get_session() as session:
                result = session.execute(query, params or {})
                return result.fetchall()
        except SQLAlchemyError as e:
            logger.error(f"Database error executing raw query: {str(e)}")
            return []
    
    def count_records(self, model_class: Type, filters: Optional[Dict[str, Any]] = None) -> int:
        """
        Count records matching specified criteria.
        
        Args:
            model_class: Model class to count
            filters: Optional filter criteria
        
        Returns:
            int: Number of matching records
        """
        try:
            with self.get_session() as session:
                query = session.query(model_class)
                
                if filters:
                    for field, value in filters.items():
                        if hasattr(model_class, field):
                            query = query.filter(getattr(model_class, field) == value)
                
                return query.count()
        except SQLAlchemyError as e:
            logger.error(f"Database error counting records: {str(e)}")
            return 0
    
    def bulk_create(self, model_instances: List, commit: bool = True) -> bool:
        """
        Create multiple records in bulk.
        
        Args:
            model_instances: List of model instances to create
            commit: Whether to commit immediately
        
        Returns:
            bool: True if bulk creation successful, False otherwise
        """
        try:
            with self.get_session() as session:
                session.bulk_save_objects(model_instances)
                if commit:
                    session.commit()
                logger.info(f"Created {len(model_instances)} records in bulk")
                return True
        except SQLAlchemyError as e:
            logger.error(f"Database error in bulk creation: {str(e)}")
            return False